<!DOCTYPE html>
<html>
<head>
    <title>003.数据表操作</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="selected">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 003.数据表操作 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">003.数据表操作</a>
<ul>
<li>
<a href="#toc_1">补充</a>
<ul>
<li>
<a href="#toc_2">USE 数据库名; --选择数据库</a>
</li>
<li>
<a href="#toc_3">GO</a>
</li>
</ul>
</li>
<li>
<a href="#toc_4">常用数据类型</a>
<ul>
<li>
<a href="#toc_5">Character 字符串：</a>
</li>
<li>
<a href="#toc_6">Unicode 字符串：</a>
</li>
<li>
<a href="#toc_7">Binary 类型：</a>
</li>
<li>
<a href="#toc_8">Number类型</a>
</li>
<li>
<a href="#toc_9">Date类型</a>
</li>
<li>
<a href="#toc_10">其他数据类型[了解]</a>
</li>
</ul>
</li>
<li>
<a href="#toc_11">创建表</a>
<ul>
<li>
<a href="#toc_12">思考</a>
</li>
</ul>
</li>
<li>
<a href="#toc_13">标识列(自动增长)</a>
</li>
<li>
<a href="#toc_14">约束</a>
<ul>
<li>
<a href="#toc_15">默认值</a>
</li>
<li>
<a href="#toc_16">非空约束</a>
</li>
<li>
<a href="#toc_17">主键</a>
</li>
<li>
<a href="#toc_18">唯一键</a>
</li>
<li>
<a href="#toc_19">检查约束</a>
</li>
<li>
<a href="#toc_20">外键约束</a>
</li>
</ul>
</li>
<li>
<a href="#toc_21">删除表</a>
</li>
<li>
<a href="#toc_22">修改表结构</a>
</li>
<li>
<a href="#toc_23">判断表或列是否存在.</a>
</li>
<li>
<a href="#toc_24">注:本章节所有练习代码</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">补充</h2>

<h3 id="toc_2">USE 数据库名; --选择数据库</h3>

<p class="md_block">
    <span class="md_line">USE //选择(跳转)到需要操作的数据库。<br /></span>
    <span class="md_line">例子：USE [ssqadm] 跳转到ssqadm数据库下，对ssqadm数据库下的对象和数据进行操作。<br /></span>
    <span class="md_line">比如刚才建的数据库,需要在那数据库<code>test</code>中创建表,先得执行一下它(<code>USE test;</code>),创建的表才在<code>test</code>数据库中.详见下面 <strong>创建表</strong> 的实例</span>
</p>


<h3 id="toc_3">GO</h3>

<p class="md_block">
    <span class="md_line">官方说法是：<br /></span>
    <span class="md_line">GO只是SQL Server管理器（SSMS）中用来提交T-SQL语句的一个标志</span>
</p>


<p class="md_block">
    <span class="md_line">我的理解:<br /></span>
    <span class="md_line">go 向 SQL Server 实用工具发出一批 Transact-SQL 语句结束的信号。go是把t-sql语句分批次执行。（一步成功了才会执行下一步,即一步一个go）</span>
</p>


<p class="md_block">
    <span class="md_line">等学习事务后,更新理解为:<br /></span>
    <span class="md_line">如果只是执行一条语句，有没有GO都一样<br /></span>
    <span class="md_line">如果多条语句之间用GO分隔开就不一样了<br /></span>
    <span class="md_line">每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。</span>
</p>


<h2 id="toc_4">常用数据类型</h2>

<h3 id="toc_5">Character 字符串：</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型</th>
<th style="text-align:left">描述</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">char(n) </td>
<td style="text-align:left">   固定长度的字符串。最多 8,000 个字符。</td>
</tr>
<tr><td style="text-align:left">varchar(n) </td>
<td style="text-align:left">    可变长度的字符串。最多 8,000 个字符。</td>
</tr>
<tr><td style="text-align:left">varchar(max) </td>
<td style="text-align:left">  可变长度的字符串。最多 1,073,741,824 个字符。</td>
</tr>
<tr><td style="text-align:left">text    可变长度的字符串。</td>
<td style="text-align:left"> 最多 2GB 字符数据。</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_6">Unicode 字符串：</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型</th>
<th style="text-align:left">描述</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">nchar(n) </td>
<td style="text-align:left">  固定长度的 Unicode 数据。最多 4,000 个字符。</td>
</tr>
<tr><td style="text-align:left">nvarchar(n) </td>
<td style="text-align:left">   可变长度的 Unicode 数据。最多 4,000 个字符。</td>
</tr>
<tr><td style="text-align:left">nvarchar(max) </td>
<td style="text-align:left"> 可变长度的 Unicode 数据。最多 536,870,912 个字符。</td>
</tr>
<tr><td style="text-align:left">ntext </td>
<td style="text-align:left"> 可变长度的 Unicode 数据。最多 2GB 字符数据。</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_7">Binary 类型：</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型    描述</th>
<th style="text-align:left"></th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">bit </td>
<td style="text-align:left">   允许 0、1 或 NULL</td>
</tr>
<tr><td style="text-align:left">binary(n) </td>
<td style="text-align:left"> 固定长度的二进制数据。最多 8,000 字节。</td>
</tr>
<tr><td style="text-align:left">varbinary(n) </td>
<td style="text-align:left">  可变长度的二进制数据。最多 8,000 字节。</td>
</tr>
<tr><td style="text-align:left">varbinary(max) </td>
<td style="text-align:left">    可变长度的二进制数据。最多 2GB 字节。</td>
</tr>
<tr><td style="text-align:left">image </td>
<td style="text-align:left"> 可变长度的二进制数据。最多 2GB。</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_8">Number类型</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型</th>
<th style="text-align:left">描述</th>
<th style="text-align:left">存储</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">tinyint </td>
<td style="text-align:left">   允许从 0 到 255 的所有数字。 </td>
<td style="text-align:left">    1 字节</td>
</tr>
<tr><td style="text-align:left">smallint </td>
<td style="text-align:left">  允许从 -32,768 到 32,767 的所有数字。 </td>
<td style="text-align:left">   2 字节</td>
</tr>
<tr><td style="text-align:left">int </td>
<td style="text-align:left">   允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 </td>
<td style="text-align:left"> 4 字节</td>
</tr>
<tr><td style="text-align:left">bigint </td>
<td style="text-align:left">    允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 </td>
<td style="text-align:left">  8 字节</td>
</tr>
<tr><td style="text-align:left">decimal(p,s) </td>
<td style="text-align:left">  固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 </td>
<td style="text-align:left">   5-17 字节</td>
</tr>
<tr><td style="text-align:left">numeric(p,s) </td>
<td style="text-align:left">  固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 </td>
<td style="text-align:left">   5-17 字节</td>
</tr>
<tr><td style="text-align:left">smallmoney </td>
<td style="text-align:left">    介于 -214,748.3648 和 214,748.3647 之间的货币数据。 </td>
<td style="text-align:left">  4 字节</td>
</tr>
<tr><td style="text-align:left">money </td>
<td style="text-align:left"> 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 </td>
<td style="text-align:left">  8 字节</td>
</tr>
<tr><td style="text-align:left">float(n) </td>
<td style="text-align:left">  从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 </td>
<td style="text-align:left">    4 或 8 字节</td>
</tr>
<tr><td style="text-align:left">real </td>
<td style="text-align:left">  从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 </td>
<td style="text-align:left"> 4 字节</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_9">Date类型</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型</th>
<th style="text-align:left">描述</th>
<th style="text-align:left">存储</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">datetime </td>
<td style="text-align:left">  从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 </td>
<td style="text-align:left">  8 bytes</td>
</tr>
<tr><td style="text-align:left">datetime2 </td>
<td style="text-align:left"> 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 </td>
<td style="text-align:left">   6-8 bytes</td>
</tr>
<tr><td style="text-align:left">smalldatetime </td>
<td style="text-align:left"> 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 </td>
<td style="text-align:left">   4 bytes</td>
</tr>
<tr><td style="text-align:left">date </td>
<td style="text-align:left">  仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 </td>
<td style="text-align:left">    3 bytes</td>
</tr>
<tr><td style="text-align:left">time </td>
<td style="text-align:left">  仅存储时间。精度为 100 纳秒。 </td>
<td style="text-align:left"> 3-5 bytes</td>
</tr>
<tr><td style="text-align:left">datetimeoffset </td>
<td style="text-align:left">    与 datetime2 相同，外加时区偏移。 </td>
<td style="text-align:left">    8-10 bytes</td>
</tr>
<tr><td style="text-align:left">timestamp </td>
<td style="text-align:left"> 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。</td>
<td style="text-align:left"></td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_10">其他数据类型[了解]</h3>
<table>
 <thead><tr><th style="text-align:left">数据类型</th>
<th style="text-align:left">描述</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">sql_variant </td>
<td style="text-align:left">   存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。</td>
</tr>
<tr><td style="text-align:left">uniqueidentifier </td>
<td style="text-align:left">  存储全局标识符 (GUID)。</td>
</tr>
<tr><td style="text-align:left">xml </td>
<td style="text-align:left">   存储 XML 格式化数据。最多 2GB。</td>
</tr>
<tr><td style="text-align:left">cursor </td>
<td style="text-align:left">    存储对用于数据库操作的指针的引用。</td>
</tr>
<tr><td style="text-align:left">table </td>
<td style="text-align:left"> 存储结果集，供稍后处理。</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h2 id="toc_11">创建表</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 1.创建数据库</span>
<span class="k">CREATE</span> <span class="k">DATABASE</span> <span class="n">test</span><span class="p">;</span>
<span class="k">GO</span>
<span class="c1">-- 2.打开(选择)数据库(切记要操作正确,会引响后面的表建语句)</span>
<span class="n">USE</span> <span class="n">test</span><span class="p">;</span>
<span class="k">GO</span>
<span class="c1">-- 3.创建表</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">stu_info1</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span>  <span class="c1">-- ID标识,后面学</span>
    <span class="p">,</span><span class="n">username</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="c1">-- 姓名</span>
    <span class="p">,</span><span class="n">age</span> <span class="n">TINYINT</span>    <span class="c1">--年龄</span>
    <span class="p">,</span><span class="n">sex</span> <span class="nb">BIT</span>    <span class="c1">-- 性别</span>
    <span class="p">,</span><span class="n">qq</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">13</span><span class="p">)</span> <span class="c1">-- QQ号码</span>
    <span class="p">,</span><span class="n">addr</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">300</span><span class="p">)</span>  <span class="c1">--地址</span>
    <span class="p">,</span><span class="n">tel</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">12</span><span class="p">)</span>    <span class="c1">-- 固话号码</span>
    <span class="p">,</span><span class="n">mobile</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">15</span><span class="p">)</span> <span class="c1">-- 手机号码</span>
    <span class="p">,</span><span class="n">email</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">30</span><span class="p">)</span>  <span class="c1">-- 电子邮箱</span>
    <span class="p">,</span><span class="n">remark</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">500</span><span class="p">)</span> <span class="c1">-- 备注</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_12">思考</h3>

<p class="md_block">
    <span class="md_line">思考:在简单讲解占用空间与数据类型后<br /></span>
    <span class="md_line">    电话号码一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    性别一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    年龄信息一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    照片信息一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    薪水一般使用什么数据类型存储?</span>
</p>


<p class="md_block">
    <span class="md_line">创建学生数据库,带学生信息表,班级班,小组表,成绩表后思考<br /></span>
    <span class="md_line">    电话号码一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    性别一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    年龄信息一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    照片信息一般使用什么数据类型存储?<br /></span>
    <span class="md_line">    薪水一般使用什么数据类型存储?<br /></span>
    <span class="md_line">对学生数据库建后后录入少部分资料后思考</span>
</p>


<h2 id="toc_13">标识列(自动增长)</h2>

<p class="md_block">
    <span class="md_line">一个表只能有一个标识列,这所说的标识列就是自动增长列,<br /></span>
    <span class="md_line">语法:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span>  <span class="c1">-- 这就是自增列</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_14">约束</h2>

<h3 id="toc_15">默认值</h3>

<p class="md_block">
    <span class="md_line">就是在插入数据时,如果该列不给值插入,它就用默认值</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span>  <span class="c1">-- 这就是自增列</span>
    <span class="p">,</span><span class="n">sex</span> <span class="nb">bit</span> <span class="k">default</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span> <span class="c1">-- 设置了默认值0</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_16">非空约束</h3>

<p class="md_block">
    <span class="md_line">自增列(identity)与主键列(primary key),它默认就是非空,所谓的非空就是在插入数据时,该列不能插入NULL值.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span>  <span class="c1">-- 这就是自增列</span>
    <span class="p">,</span><span class="n">username</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="c1">-- 非空,不能插入NULL值</span>
    <span class="p">,</span><span class="n">sex</span> <span class="nb">bit</span> <span class="k">default</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span> <span class="c1">-- 设置了默认值0</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_17">主键</h3>

<p class="md_block">
    <span class="md_line">这才是表的真正标识列.它有排序,值要求唯一.非空约束.一个表建议必须有一个主键,它可以是一列或多列.<br /></span>
    <span class="md_line">一个表中只有一个主键.但一个主键可以是多列</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 创建单列主键.</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">primary</span> <span class="k">key</span> <span class="c1">-- 这就是自增列</span>
<span class="p">);</span>

<span class="c1">-- 创建多列主键</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">score</span><span class="p">](</span>
    <span class="n">uid</span> <span class="nb">int</span>     <span class="c1">-- 用户ID(用户表的主键)</span>
    <span class="p">,</span><span class="n">sid</span> <span class="nb">int</span>        <span class="c1">-- 科目ID(科目表的主键)</span>
    <span class="p">,</span><span class="n">score</span>      <span class="c1">-- 分数</span>
    <span class="p">,</span><span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">uid</span><span class="p">,</span><span class="n">sid</span><span class="p">)</span> <span class="c1">-- 多列主键</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_18">唯一键</h3>

<p class="md_block">
    <span class="md_line">它有几种叫法:唯一索引,唯一键,唯一约束.<br /></span>
    <span class="md_line">它有个特点,当列所有的行中值不能重复.但允许为NULL.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">primary</span> <span class="k">key</span> <span class="c1">-- 这就是自增列</span>
    <span class="p">,</span><span class="n">card_id</span> <span class="nb">char</span><span class="p">(</span><span class="mi">18</span><span class="p">)</span> <span class="k">unique</span> <span class="c1">-- 唯一约束,就是身份证号码列中的值不能重复.</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_19">检查约束</h3>

<p class="md_block">
    <span class="md_line">限制值的范围.它是一个条件.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">users</span><span class="p">](</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">primary</span> <span class="k">key</span> <span class="c1">-- 这就是自增列</span>
    <span class="p">,</span><span class="n">age</span> <span class="nb">smallint</span> <span class="k">check</span><span class="p">(</span><span class="n">age</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">and</span> <span class="n">age</span> <span class="o">&lt;=</span> <span class="mi">200</span><span class="p">)</span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_20">外键约束</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 学生用户信息表</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">user_info</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span> <span class="k">primary</span> <span class="k">key</span> 
    <span class="p">,</span><span class="n">username</span> <span class="n">nvarchar</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="c1">--NVARCHAR()表示支持多字节字符.比如中文</span>
<span class="p">);</span>

<span class="c1">-- 收货地址表(一个人可以有N个收货地址.)</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">user_addr</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">int</span> <span class="k">identity</span> <span class="k">primary</span> <span class="k">key</span>
    <span class="p">,</span><span class="n">uid</span> <span class="nb">int</span> <span class="c1">-- 用户id,表示这条数据是哪个用户的.</span>
    <span class="p">,</span><span class="n">addr</span> <span class="n">nvarchar</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span>
    <span class="p">,</span><span class="k">foreign</span> <span class="k">key</span> <span class="p">(</span><span class="n">uid</span><span class="p">)</span> <span class="k">references</span> <span class="n">user_info</span><span class="p">(</span><span class="n">id</span><span class="p">)</span>
    <span class="c1">-- 注意,外键后面可以跟参数,表示关联后父表中数据操作时,子表怎么办??</span>
    <span class="c1">-- ON DELETE CASCADE ON UPDATE SET NULL  -- 表与表之间的关联.   ON UPDATE SET DEFAULT</span>
    <span class="c1">-- 它有三种操作,级联(子表随父表一起操作),设NULL(父表中数据操作时,子表中外键列设NULL),设默认值.  如:ON UPDATE SET DEFAULT(就是父表中关联列数据修改时,子表关联列设默认值.) </span>
<span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_21">删除表</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">DROP</span> <span class="k">TABLE</span> <span class="err">表名</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_22">修改表结构</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 修改表:====================================================================================================</span>
<span class="c1">-- 表重命名</span>
    <span class="c1">-- EXEC sp_rename &#39;原表名&#39;,&#39;新表名&#39;;</span>
    <span class="k">EXEC</span> <span class="n">sp_rename</span> <span class="s1">&#39;stu_info&#39;</span><span class="p">,</span><span class="s1">&#39;student_info&#39;</span><span class="p">;</span>
<span class="c1">-- 删除表:如果该表被关联,则不能删除(或先删除有外键关联它的表)</span>
    <span class="c1">-- DROP TABLE 表名;</span>
    <span class="k">DROP</span> <span class="k">TABLE</span> <span class="n">stu_score</span><span class="p">;</span>
    <span class="k">DROP</span> <span class="k">TABLE</span> <span class="n">stu_info</span><span class="p">;</span>
<span class="c1">-- 修改列名</span>
    <span class="c1">-- EXEC sp_rename &#39;表名.列名&#39;,&#39;新列名&#39;,&#39;COLUMN&#39;;</span>
    <span class="k">EXEC</span> <span class="n">sp_rename</span> <span class="s1">&#39;stu_info.username&#39;</span><span class="p">,</span><span class="s1">&#39;uname&#39;</span><span class="p">,</span><span class="s1">&#39;COLUMN&#39;</span><span class="p">;</span>

<span class="c1">-- 增加列(同时可以增加:非空约束,默认值,自增[要注意,自增列不能重复增加到表中])</span>
    <span class="c1">-- ALTER TABLE stu_info ADD remarks VARCHAR(500) NOT NULL DEFAULT &#39;&#39;;</span>
    <span class="c1">-- ALTER TABLE stu_info ADD remarks1 VARCHAR(500),remarks2 VARCHAR(500);</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">ADD</span> <span class="n">remarks</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">500</span><span class="p">);</span>

<span class="c1">-- 删除列(如果列上有约束,必面先删除约束)</span>
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN remarks;</span>
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN remarks1,remarks2;</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">DROP</span> <span class="k">COLUMN</span> <span class="n">remarks</span><span class="p">;</span>


<span class="c1">-- 修改列类型(包括NOT NULL,如果列上有约束,必须先删除)</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">ALTER</span> <span class="k">COLUMN</span> <span class="n">remarks</span> <span class="nb">INT</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">;</span>
    <span class="c1">-- ALTER TABLE stu_info MODIFY COLUMN remarks INT NOT NULL;</span>
    

<span class="c1">-- 修改identity列:自增列不能直接修改，必须将原有自增列删除，然后重新添加一列具有identity属性的字段。</span>
    <span class="c1">-- alter table 表名 drop column ID</span>
    <span class="c1">-- alter table 表名 add ID int identity(1,1)</span>
    
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN id;</span>
    <span class="c1">-- ALTER TABLE stu_info DROP CONSTRAINT PK__stu_info__3213E83F00750D23;</span>
    <span class="c1">-- ALTER TABLE stu_info ADD id INT IDENTITY(1,1);</span>
    

<span class="c1">-- 为了练习表的修改,我们先建个最简单的表,修改到合适为止</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">stu_score</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span>
<span class="p">);</span>

<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="n">uid</span> <span class="nb">INT</span><span class="p">,</span><span class="n">subid</span> <span class="nb">INT</span><span class="p">,</span><span class="n">score1</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">,</span><span class="n">score2</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">,</span><span class="n">score</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">DROP</span> <span class="k">COLUMN</span> <span class="n">id</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="n">id</span> <span class="nb">INT</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score1</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score1</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score1</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score2</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score2</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score2</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">FK_stu_score_uid</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">uid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">stu_info</span><span class="p">(</span><span class="n">id</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">subid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">score_sub</span><span class="p">(</span><span class="n">id</span><span class="p">);</span>

<span class="cm">/*</span>
<span class="cm">* 约束名的命名规范:</span>
<span class="cm">*   检查约束(CHECK):CK__表名__列名</span>
<span class="cm">*   默认值(DEFAULT):DF__表名__列名</span>
<span class="cm">*   唯一约束(UNIQUE):UQ__表名__列名</span>
<span class="cm">*   主键约束(PRIMARY KEY): PK__表名__列名</span>
<span class="cm">*   外键约束(FOREIGN KEY): FK__表名__列名</span>
<span class="cm">*/</span>
    

<span class="c1">-- 增加约束</span>
    <span class="c1">-- 由于原列没有自增,在这所以要删除后重建</span>
        <span class="c1">-- ALTER TABLE stu_score DROP COLUMN id;</span>
        <span class="c1">-- ALTER TABLE stu_score ADD id INT NOT NULL IDENTITY;</span>
    <span class="c1">-- 增加主键约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CONSTRAINT PK_stu_score_huodeming PRIMARY KEY(id);</span>
    <span class="c1">-- 增加唯一约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD UNIQUE(score1);</span>
    <span class="c1">-- 增加检查约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CHECK(score2 &gt;= 0 AND score2 &lt;= 100);</span>
    <span class="c1">-- 增加外键约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CONSTRAINT FK_subid_hdm FOREIGN KEY (subid) REFERENCES score_sub(id);</span>
        <span class="c1">-- ALTER TABLE stu_score ADD FOREIGN KEY (uid) REFERENCES stu_info(id);</span>
    <span class="c1">-- 增加默认值</span>
        <span class="c1">-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 0 FOR 列名;</span>
        <span class="c1">-- ALTER TABLE stu_info ADD CONSTRAINT DF_remarks DEFAULT 0 FOR remarks;</span>

<span class="c1">-- 删除约束</span>
    <span class="c1">-- ALTER TABLE stu_info DROP CONSTRAINT DF__stu_info__remark__46486B8E;</span>
    <span class="c1">-- ALTER TABLE stu_score DROP CONSTRAINT PK_stu_score_huodeming;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_23">判断表或列是否存在.</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 判断表,列是否存在</span>
    <span class="c1">-- SELECT * FROM sys.objects WHERE NAME = &#39;stu_info&#39; AND [type]=&#39;U&#39;;</span>
    <span class="c1">-- SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(&#39;stu_info&#39;) AND NAME = &#39;age&#39;;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_24">注:本章节所有练习代码</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 创建表的学习</span>

<span class="c1">-- 1.创建数据库</span>
<span class="k">CREATE</span> <span class="k">DATABASE</span> <span class="p">[</span><span class="n">student</span><span class="p">]</span>
<span class="k">ON</span>
<span class="p">(</span>
    <span class="n">NAME</span><span class="o">=</span><span class="ss">&quot;student&quot;</span>
    <span class="p">,</span><span class="n">FILENAME</span><span class="o">=</span><span class="ss">&quot;D:\dbs\student.mdf&quot;</span>
    <span class="p">,</span><span class="k">SIZE</span><span class="o">=</span><span class="mi">5</span><span class="n">MB</span>
    <span class="p">,</span><span class="n">FILEGROWTH</span><span class="o">=</span><span class="mi">1</span><span class="n">MB</span>
<span class="p">)</span><span class="n">LOG</span> <span class="k">ON</span><span class="p">(</span>
    <span class="n">NAME</span><span class="o">=</span><span class="ss">&quot;student_log&quot;</span>
    <span class="p">,</span><span class="n">FILENAME</span><span class="o">=</span><span class="ss">&quot;D:\dbs\student_log.ldf&quot;</span>
    <span class="p">,</span><span class="k">SIZE</span><span class="o">=</span><span class="mi">1</span><span class="n">MB</span>
    <span class="p">,</span><span class="n">FILEGROWTH</span><span class="o">=</span><span class="mi">1</span><span class="n">MB</span>
    <span class="p">,</span><span class="n">MAXSIZE</span><span class="o">=</span><span class="mi">100</span><span class="n">MB</span>
<span class="p">);</span>

<span class="k">GO</span>
<span class="c1">-- 2.选择下面需要操作的数据库</span>
<span class="n">USE</span> <span class="n">student</span><span class="p">;</span>

<span class="k">GO</span>
<span class="c1">-- 3.创建表  </span>
    <span class="c1">-- 自动增长(标识列):也是表的标识列,一个表只需一列标识列,它是不允许显式插入值的,会自动生成.</span>
    <span class="c1">-- NOT NULL(不允许为空): 一般建表时列默认都是允许为空的,所以只有不允许为空的列后加 NOT NULL,标识列(自增与主键列)不加NOT NULL它也会自动加上.</span>
    <span class="c1">-- DEFAULT(默认值): 为某列设置默认值,在插入数据时,如如不显示为该列插入值,它就用默认值.可以写成 DEFAULT(0) 或 DEFAULT 0 </span>
    <span class="c1">-- PRIMARY KEY(主键):一个表必须有一个主键,这个主键可以是单列,也可以是多列.主键的值必须是唯一的.不能重复.</span>
    <span class="c1">-- UNIQUE(唯一约束,也叫唯一索引): 被设置的列的值必须是唯一的</span>
    <span class="c1">-- CHECK(检查约束):检查某列的值是否合法.(限定值的范围)</span>
    <span class="c1">-- FOREIGN KEY(外键约束):就是表与表之间的关系锁定.表与表之间存在关系,就必须有关联的列(外键所在表的某一列去关联别的表的某一列)....</span>
        <span class="c1">-- 注意,被外键关联的数据默认是不能修改主键或删除的,但设置外键时可以指定相关操作</span>
        <span class="c1">-- ON DELETE | UPDATE CASCADE  级联操作,删除或修改被关联的数据时,同时也删除或修改主动去关联的数据</span>
        <span class="c1">-- ON DELETE | UPDATE SET NULL 级联操作,把主键动去关联的外键的值设为NULL</span>
        <span class="c1">-- ON DELETE | UPDATE SET DEFAULT 级联操作,把主动去关联的外键的值设为默认值</span>
        <span class="c1">-- 总结,外键是一个很特殊,很麻烦的键.</span>

<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">stu_info</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">PRIMARY</span> <span class="k">KEY</span>   <span class="c1">-- 自动为该列设置值,且从1开始,每次增加一行数据,它自动+1</span>
    <span class="p">,</span><span class="n">username</span> <span class="n">NVARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">UNIQUE</span> <span class="c1">-- 设置了不允许不空,以后插入数据时就必须要为该列插入值.</span>
    <span class="p">,</span><span class="n">age</span> <span class="n">TINYINT</span>
    <span class="p">,</span><span class="n">sex</span> <span class="nb">BIT</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span> <span class="c1">-- 不允许为空,设置默认值</span>
    <span class="p">,</span><span class="n">addr</span> <span class="n">NVARCHAR</span><span class="p">(</span><span class="mi">300</span><span class="p">)</span>
    <span class="p">,</span><span class="n">qq</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">100</span><span class="p">)</span>
    <span class="p">,</span><span class="n">wechat</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">100</span><span class="p">)</span>
    <span class="p">,</span><span class="n">tel</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
    <span class="p">,</span><span class="n">mobile</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">55</span><span class="p">)</span>
    <span class="p">,</span><span class="n">car_id</span> <span class="nb">CHAR</span><span class="p">(</span><span class="mi">18</span><span class="p">)</span>
<span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">stu_info</span><span class="p">(</span><span class="n">username</span><span class="p">,</span><span class="n">sex</span><span class="p">)</span> <span class="k">VALUES</span><span class="p">(</span><span class="s1">&#39;李金涛&#39;</span><span class="p">,</span><span class="mi">1</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">stu_info</span><span class="p">(</span><span class="n">username</span><span class="p">,</span><span class="n">sex</span><span class="p">)</span> <span class="k">VALUES</span><span class="p">(</span><span class="s1">&#39;阳邓拓&#39;</span><span class="p">,</span><span class="mi">1</span><span class="p">);</span>

<span class="c1">-- 多列主键的创建</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">stu_score</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span>
    <span class="p">,</span><span class="n">uid</span> <span class="nb">INT</span>        <span class="c1">-- 用户ID     1</span>
    <span class="p">,</span><span class="n">subid</span> <span class="nb">INT</span>  <span class="c1">-- 科目ID     2</span>
    <span class="p">,</span><span class="n">score1</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score1</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score1</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">)</span> <span class="c1">-- 理论成绩</span>
    <span class="p">,</span><span class="n">score2</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score2</span> <span class="k">BETWEEN</span> <span class="mi">0</span> <span class="k">AND</span> <span class="mi">100</span><span class="p">)</span> <span class="c1">-- 上机成绩 BETWEEN后面会学习.</span>
    <span class="p">,</span><span class="n">score</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span><span class="c1">-- 总成绩</span>
    <span class="c1">-- ,PRIMARY KEY(uid,subid) -- 设置主键的第2种方法,通用些.</span>
    <span class="c1">-- ,UNIQUE(score,score1) -- 设置唯一约束的第二种方法</span>
    <span class="p">,</span><span class="k">CHECK</span><span class="p">(</span><span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">)</span> <span class="c1">-- 设置检查约束的第二种方法</span>
    <span class="p">,</span><span class="k">FOREIGN</span> <span class="k">KEY</span><span class="p">(</span><span class="n">uid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">stu_info</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">ON</span> <span class="k">DELETE</span> <span class="k">CASCADE</span> <span class="k">ON</span> <span class="k">UPDATE</span> <span class="k">SET</span> <span class="k">NULL</span>  <span class="c1">-- 表与表之间的关联.</span>
    <span class="p">,</span><span class="k">FOREIGN</span> <span class="k">KEY</span><span class="p">(</span><span class="n">subid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">score_sub</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">ON</span> <span class="k">DELETE</span> <span class="k">CASCADE</span> <span class="k">ON</span> <span class="k">UPDATE</span> <span class="k">CASCADE</span> <span class="c1">-- 表与表之间的关联</span>
<span class="p">);</span>

<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">score_sub</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="k">IDENTITY</span>  <span class="c1">--IDENTIT它的默认值就是(1,1),所以可以简写</span>
    <span class="p">,</span><span class="n">sub_name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="p">);</span>

<span class="c1">-- INSERT INTO stu_score(uid,subid,score1,score2,score) VALUES(1,4,40,50,101)</span>


<span class="c1">-- 修改表:====================================================================================================</span>
<span class="c1">-- 表重命名</span>
    <span class="c1">-- EXEC sp_rename &#39;原表名&#39;,&#39;新表名&#39;;</span>
    <span class="k">EXEC</span> <span class="n">sp_rename</span> <span class="s1">&#39;stu_info&#39;</span><span class="p">,</span><span class="s1">&#39;student_info&#39;</span><span class="p">;</span>
<span class="c1">-- 删除表:如果该表被关联,则不能删除(或先删除有外键关联它的表)</span>
    <span class="c1">-- DROP TABLE 表名;</span>
    <span class="k">DROP</span> <span class="k">TABLE</span> <span class="n">stu_score</span><span class="p">;</span>
    <span class="k">DROP</span> <span class="k">TABLE</span> <span class="n">stu_info</span><span class="p">;</span>
<span class="c1">-- 修改列名</span>
    <span class="c1">-- EXEC sp_rename &#39;表名.列名&#39;,&#39;新列名&#39;,&#39;COLUMN&#39;;</span>
    <span class="k">EXEC</span> <span class="n">sp_rename</span> <span class="s1">&#39;stu_info.username&#39;</span><span class="p">,</span><span class="s1">&#39;uname&#39;</span><span class="p">,</span><span class="s1">&#39;COLUMN&#39;</span><span class="p">;</span>

<span class="c1">-- 增加列(同时可以增加:非空约束,默认值,自增[要注意,自增列不能重复增加到表中])</span>
    <span class="c1">-- ALTER TABLE stu_info ADD remarks VARCHAR(500) NOT NULL DEFAULT &#39;&#39;;</span>
    <span class="c1">-- ALTER TABLE stu_info ADD remarks1 VARCHAR(500),remarks2 VARCHAR(500);</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">ADD</span> <span class="n">remarks</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">500</span><span class="p">);</span>

<span class="c1">-- 删除列(如果列上有约束,必面先删除约束)</span>
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN remarks;</span>
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN remarks1,remarks2;</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">DROP</span> <span class="k">COLUMN</span> <span class="n">remarks</span><span class="p">;</span>


<span class="c1">-- 修改列类型(包括NOT NULL,如果列上有约束,必须先删除)</span>
    <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_info</span> <span class="k">ALTER</span> <span class="k">COLUMN</span> <span class="n">remarks</span> <span class="nb">INT</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">;</span>
    <span class="c1">-- ALTER TABLE stu_info MODIFY COLUMN remarks INT NOT NULL;</span>
    

<span class="c1">-- 修改identity列:自增列不能直接修改，必须将原有自增列删除，然后重新添加一列具有identity属性的字段。</span>
    <span class="c1">-- alter table 表名 drop column ID</span>
    <span class="c1">-- alter table 表名 add ID int identity(1,1)</span>
    
    <span class="c1">-- ALTER TABLE stu_info DROP COLUMN id;</span>
    <span class="c1">-- ALTER TABLE stu_info DROP CONSTRAINT PK__stu_info__3213E83F00750D23;</span>
    <span class="c1">-- ALTER TABLE stu_info ADD id INT IDENTITY(1,1);</span>
    

<span class="c1">-- 为了练习表的修改,我们先建个最简单的表,修改到合适为止</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">stu_score</span><span class="p">(</span>
    <span class="n">id</span> <span class="nb">INT</span>
<span class="p">);</span>

<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="n">uid</span> <span class="nb">INT</span><span class="p">,</span><span class="n">subid</span> <span class="nb">INT</span><span class="p">,</span><span class="n">score1</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">,</span><span class="n">score2</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">4</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">,</span><span class="n">score</span> <span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="k">DEFAULT</span> <span class="mi">0</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">DROP</span> <span class="k">COLUMN</span> <span class="n">id</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="n">id</span> <span class="nb">INT</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">;</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score1</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score1</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score1</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score2</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score2</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score2</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">CK_stu_score_score</span> <span class="k">CHECK</span><span class="p">(</span><span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">AND</span> <span class="n">score</span> <span class="o">&lt;=</span> <span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">CONSTRAINT</span> <span class="n">FK_stu_score_uid</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">uid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">stu_info</span><span class="p">(</span><span class="n">id</span><span class="p">);</span>
<span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">stu_score</span> <span class="k">ADD</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">subid</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">score_sub</span><span class="p">(</span><span class="n">id</span><span class="p">);</span>

<span class="cm">/*</span>
<span class="cm">* 约束名的命名规范:</span>
<span class="cm">*   检查约束(CHECK):CK__表名__列名</span>
<span class="cm">*   默认值(DEFAULT):DF__表名__列名</span>
<span class="cm">*   唯一约束(UNIQUE):UQ__表名__列名</span>
<span class="cm">*   主键约束(PRIMARY KEY): PK__表名__列名</span>
<span class="cm">*   外键约束(FOREIGN KEY): FK__表名__列名</span>
<span class="cm">*/</span>
    

<span class="c1">-- 增加约束</span>
    <span class="c1">-- 由于原列没有自增,在这所以要删除后重建</span>
        <span class="c1">-- ALTER TABLE stu_score DROP COLUMN id;</span>
        <span class="c1">-- ALTER TABLE stu_score ADD id INT NOT NULL IDENTITY;</span>
    <span class="c1">-- 增加主键约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CONSTRAINT PK_stu_score_huodeming PRIMARY KEY(id);</span>
    <span class="c1">-- 增加唯一约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD UNIQUE(score1);</span>
    <span class="c1">-- 增加检查约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CHECK(score2 &gt;= 0 AND score2 &lt;= 100);</span>
    <span class="c1">-- 增加外键约束</span>
        <span class="c1">-- ALTER TABLE stu_score ADD CONSTRAINT FK_subid_hdm FOREIGN KEY (subid) REFERENCES score_sub(id);</span>
        <span class="c1">-- ALTER TABLE stu_score ADD FOREIGN KEY (uid) REFERENCES stu_info(id);</span>
    <span class="c1">-- 增加默认值</span>
        <span class="c1">-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 0 FOR 列名;</span>
        <span class="c1">-- ALTER TABLE stu_info ADD CONSTRAINT DF_remarks DEFAULT 0 FOR remarks;</span>

<span class="c1">-- 删除约束</span>
    <span class="c1">-- ALTER TABLE stu_info DROP CONSTRAINT DF__stu_info__remark__46486B8E;</span>
    <span class="c1">-- ALTER TABLE stu_score DROP CONSTRAINT PK_stu_score_huodeming;</span>


<span class="c1">-- 判断表,列是否存在,还没写条件</span>
    <span class="c1">-- SELECT * FROM sys.objects WHERE NAME = &#39;stu_info&#39; AND [type]=&#39;U&#39;;</span>
    <span class="c1">-- SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(&#39;stu_info&#39;) AND NAME = &#39;age&#39;;</span>



<span class="c1">-- =============下面代码是数据类型练习用过的==============</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">tb2</span><span class="p">(</span>
    <span class="c1">-- 列名  类型</span>
    <span class="n">col01</span> <span class="nb">CHAR</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span>   
    <span class="p">,</span><span class="n">col02</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span>
    <span class="p">,</span><span class="n">col03</span> <span class="nb">TEXT</span>
<span class="p">);</span>


<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">tb4</span><span class="p">(</span>
    <span class="c1">-- 列名  类型</span>
    <span class="n">c1</span> <span class="nb">bit</span>      <span class="c1">-- true 或 false  </span>
    <span class="p">,</span><span class="n">c2</span> <span class="n">tinyint</span> <span class="c1">-- 0-255</span>
    <span class="p">,</span><span class="n">c3</span> <span class="nb">smallint</span> <span class="c1">-- 3万多</span>
    <span class="p">,</span><span class="n">c4</span> <span class="nb">int</span> <span class="c1">-- 21亿多</span>
    <span class="p">,</span><span class="n">c5</span> <span class="nb">bigint</span> <span class="c1">-- 长整型</span>
    <span class="p">,</span><span class="n">c6</span> <span class="nb">float</span>   <span class="c1">--近似值小数</span>
    <span class="p">,</span><span class="n">c7</span> <span class="nb">real</span>    <span class="c1">--近似值小数</span>
    <span class="p">,</span><span class="n">c8</span> <span class="nb">decimal</span><span class="p">(</span><span class="mi">20</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span>   <span class="c1">-- 精确小数,共5位,2位小数(注意最大值999.55)</span>
    <span class="p">,</span><span class="n">c9</span> <span class="nb">numeric</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span>    <span class="c1">-- 精确小数,共5位,2位小数(注意最大值99.9)</span>
    <span class="p">,</span><span class="n">c10</span> <span class="n">money</span>  <span class="c1">-- 用来存储钱的.....(默认4位小数)</span>
<span class="p">);</span>
<span class="c1">-- INSERT INTO tb1 values(&#39;1234567890&#39;,&#39;1234567890&#39;)</span>
<span class="c1">-- INSERT INTO tb3(c1,c2,c3,c4,c5) VALUES(0,256,30000,1,1);</span>
<span class="c1">-- INSERT INTO tb3(c6,c7,c8,c9,c10) VALUES(9.123456,10.123789,999.99,99.9,10.250);</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>